package cn.boltzjf.sql4j.examples.mysql;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.UUID;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import cn.boltzjf.sql4j.Row;
import cn.boltzjf.sql4j.Sql;
import cn.boltzjf.sql4j.examples.mysql.dao.StudentDao;
import cn.boltzjf.sql4j.examples.mysql.db.Db;
import cn.boltzjf.sql4j.ext.mysql.MysqlTypeUtils;

public class MysqlExample {
    
    private static final Logger log = LoggerFactory.getLogger(MysqlExample.class);
    
    private static StudentDao stuDao = new StudentDao();
    
    private static Connection getConn() throws SQLException {
        return DriverManager.getConnection("jdbc:mysql://localhost:13306/ruby", "ruby", "password");
    }

    public static void main(String[] args) throws Exception {
        try (Connection conn = getConn()) {
            UUID tenantUuid = UUID.randomUUID();
            Row stuRow = new Row();
            UUID stuUuid = UUID.randomUUID();
            stuRow.set(Db.student.tenantId, tenantUuid);
            stuRow.set(Db.student.id, stuUuid);
            stuRow.set(Db.student.name, "Zhang San");
            stuRow.set(Db.student.studentNumber, "8411133");
            stuRow.set(Db.student.family, "{\"father\":\"Zhang Dad\"}");
            stuRow.set(Db.student.customFields, "{\"father\":\"Zhang Dad\"}");
            stuDao.insert(conn, stuRow);
            
            stuRow.set(Db.student.enrollYear, "1998");
            stuDao.update(conn, stuRow, Db.student.id, Db.student.tenantId);
            
            stuRow = new Row();
            stuRow.set(Db.student.id, stuUuid);
            stuRow.set(Db.student.tenantId, tenantUuid);
            stuRow.set(Db.student.enrollYear, "1998");
            stuDao.update(conn, stuRow, Db.student.id, Db.student.tenantId);
            
            stuRow = new Row();
            stuRow.set(Db.student.deleted, false);
            stuDao.update(conn, stuRow);
            
            Row by = new Row();
            by.set(Db.student.deleted, true);
            stuDao.delete(conn, by);
            
            by = new Row();
            by.set(Db.student.deleted, false);
            by.set(Db.student.family, null);
            List<Row> rows = stuDao.getList(conn, by, Db.student.id, Db.student.name, Db.student.studentNumber, Db.student.enrollYear);
            rows.forEach(row -> {log.debug("Row: {}", row);});
            
            by = new Row();
            by.set(Db.student.deleted, false);
            rows = stuDao.getList(conn, by, Db.student.id, Db.student.name, Db.student.studentNumber, Db.student.enrollYear, Db.student.family, Db.student.customFields);
            rows.forEach(row -> {log.debug("Row: {}", row);});
            
            Sql sql = new Sql("update #{table} set #{highestScore}=?{score} where #{tid}=?{tid} and #{id}=?{id}")
                .mapName("table", Db.student)
                .mapName("highestScore", Db.student.highestScore)
                .mapParam("score", new BigDecimal("98.5"))
                .mapNameAndParam("tid", Db.student.tenantId, tenantUuid.toString(), "uuid_to_bin(?)")
                .mapNameAndParam("id", Db.student.id, stuUuid.toString(), "uuid_to_bin(?)");
            int updated = sql.executeUpdate(conn);
            log.debug("Updated {} students", updated);
            
            sql = new Sql("update #{table} set #{highestScore}=?{score} where #{tid}=uuid_to_bin(?{tid}) and #{id}=uuid_to_bin(?{id})")
                    .mapName("table", Db.student)
                    .mapName("highestScore", Db.student.highestScore)
                    .mapParam("score", new BigDecimal("98.5"))
                    .mapNameAndParam("tid", Db.student.tenantId, tenantUuid.toString())
                    .mapNameAndParam("id", Db.student.id, stuUuid.toString());
                updated = sql.executeUpdate(conn);
                log.debug("Updated {} students", updated);
            
            sql = new Sql("update #{table} set #{highestScore}=?{score} where #{tid}=?{tid} and #{id}=?{id}")
                .mapName("table", Db.student)
                .mapName("highestScore", Db.student.highestScore)
                .mapParam("score", new BigDecimal("98.5"))
                .mapNameAndParam("tid", Db.student.tenantId, MysqlTypeUtils.uuidToBin(tenantUuid))
                .mapNameAndParam("id", Db.student.id, MysqlTypeUtils.uuidToBin(stuUuid));
            updated = sql.executeUpdate(conn);
            log.debug("Updated {} students", updated);
            
            batchInsertStudents();
        }
    }
    
    private static void batchInsertStudents() throws Exception {
    	List<Row> students = new ArrayList<Row>();
    	for (int i = 0; i < 10; ++i) {
	    	UUID tenantUuid = UUID.randomUUID();
	        Row stuRow = new Row();
	        UUID stuUuid = UUID.randomUUID();
	        stuRow.set(Db.student.tenantId, tenantUuid);
	        stuRow.set(Db.student.id, stuUuid);
	        stuRow.set(Db.student.name, String.format("Student %02d", i));
	        stuRow.set(Db.student.studentNumber, String.format("84111-%02d", i));
	        stuRow.set(Db.student.family, "{\"father\":\"A Dad\"}");
	        stuRow.set(Db.student.customFields, "{\"father\":\"A Dad\"}");
	        students.add(stuRow);
    	}
    	Connection conn = getConn();
    	log.debug("Batch inserting:");
		int[] rs = stuDao.batchInsert(conn, students);
		int count = 0;
		for (int r : rs) {
			count += r;
		}
		log.debug("Batach inserted {} students", count);
		
		List<Row> updates = new ArrayList<>(students.size());
		for (Row old : students) {
			Row stuRow = new Row();
			stuRow.set(Db.student.tenantId, old.get(Db.student.tenantId));
			stuRow.set(Db.student.id, old.get(Db.student.id));
			stuRow.set(Db.student.enrollYear, "2000");
			stuRow.set(Db.student.highestScore, new BigDecimal("88.66"));
			updates.add(stuRow);
		}
		log.debug("Batch updating:");
		rs = stuDao.batchUpdate(conn, updates, Db.student.tenantId, Db.student.id);
		count = 0;
		for (int r : rs) {
			count += r;
		}
		log.debug("Batch updated {} students", count);
    }

}
